import requests
import json

from langchain.prompts import PromptTemplate
from boat_nl2sql.generate_echarts import visualize_sql_result, analyze_data_for_visualization, convert_sql_result_to_dataframe

API_URL = 'http://10.1.161.53:11434/api/chat'
MODEL_NAME = "deepseek-r1:70b"

# 在这里导入您的 LLM
from boat_nl2sql.llm_module import SiliconFlow
llm = SiliconFlow()

def generate_html(question, sql_result, extracted_sql):
    try:
        # 可视化SQL结果
        output_file = visualize_sql_result(
            sql_result=sql_result,
            query=extracted_sql,
            question=question
        )

        if output_file:
            print(f"可视化已完成，请打开以下文件查看: {output_file}")

            # 生成分析结果
            TEXT_PROMPT = PromptTemplate.from_template(
                """根据问题{question}，SQL查询结果{sql_result}，以及生成的图表类型{chart_type}，  
                请提供以下内容：  
                1. 对数据的简明分析  
                2. 从数据中得出的关键见解  
                3. 为什么选择了这种图表类型来展示数据  
                4. 根据数据可能的建议或行动计划  

                请使用简洁专业的语言回答。"""
            )

            # 从可视化推荐中获取图表类型
            recommendation = analyze_data_for_visualization(
                convert_sql_result_to_dataframe(sql_result),
                extracted_sql,
                question
            )

            chart_type = recommendation["chart_type"]

            # 格式化PromptTemplate为字符串
            formatted_prompt = TEXT_PROMPT.format(
                test_question=question,
                sql_result=sql_result,
                chart_type=chart_type
            )

            data = {
                "model": MODEL_NAME,
                "messages": [
                    {"role": "system", "content": "你是一个专业的图表生成专家和html生成专家"},
                    {"role": "user", "content": formatted_prompt}
                ],
                "stream": True
            }

            response = requests.post(
                API_URL,
                data=json.dumps(data),
                stream=True,
                headers={'Content-Type': 'application/json'}
            )
            response.raise_for_status()

            # # 返回生成器，逐块返回内容
            # for chunk in response.iter_lines():
            #     if chunk:
            #         decoded = json.loads(chunk.decode('utf-8'))
            #         if 'message' in decoded and 'content' in decoded['message']:
            #             content = decoded['message']['content']
            #             yield content  # 直接返回每个内容块

    except Exception as e:
        print(f"执行查询时出错: {e}")

# 使用示例（外部处理流式响应）
if __name__ == "__main__":
    question = '统计一月份各卡口点的船舶通过数量，按照通过船舶数量降序排列，只显示通过量前10的卡口。'
    # 生成SQL查询
    sql_result = [('黄浦江(松浦大桥南岸)', 24744), ('杭申线卡口', 14335), ('苏申内港线(上港宜东)', 6897), ('平申线省际检查站', 4634), ('大治河船闸', 4341),
                  ('苏申外港线省际检查站', 3767), ('苏申内港线省际检查站', 3471), ('杨思船闸口', 2495), ('长湖申线卡口', 1797), ('油墩港(油墩港船闸)', 1749)]

    extracted_sql = '''SELECT * FROM ( SELECT b.NAME AS 卡口名称, COUNT(d.CODE) AS 通过船舶数量 FROM BAYONET_BASICS.SYS_BAYONET b JOIN BAYONET_DYNAMIC.DATAFUSION d ON b.ID = d.BAYONET_ID WHERE d.PASSTIME >= TO_DATE('2025-01-01', 'YYYY-MM-DD') AND d.PASSTIME < TO_DATE('2025-02-01', 'YYYY-MM-DD') GROUP BY b.NAME ORDER BY 通过船舶数量 DESC ) WHERE ROWNUM <= 10'''

    print("\n开始生成html：")
    try:
        # 遍历生成器逐块处理
        for content in generate_html(question, sql_result, extracted_sql):
            print(content, end="", flush=True)  # 外部处理实时输出
    except Exception as e:
        print(f"请求失败: {str(e)}")
    finally:
        print("\nhtml生成完成")